package com.ruangong.GitClient.app.TravelExpense;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

public class TravelDBHelper extends SQLiteOpenHelper {
    // Database Info
    private static final String DATABASE_NAME = "travel_events_db";
    private static final int DATABASE_VERSION = 1;

    // Table Names
    public static final String TABLE_EVENTS = "events";
    private static final String TABLE_EXPENSES = "expenses";

    // Common column names
    public static final String COLUMN_ID = "id";

    // EVENTS Table - column names
    public static final String COLUMN_EVENT_NAME = "event_name";
    public static final String COLUMN_NUMBER_OF_PEOPLE = "number_of_people";

    // EXPENSES Table - column names
    private static final String KEY_EVENT_ID = "event_id";
    private static final String KEY_EXPENSE_NAME = "expense_name";
    private static final String KEY_AMOUNT = "amount";

    // Table Create Statements
    // Events table create statement
    private static final String CREATE_TABLE_EVENTS = "CREATE TABLE " + TABLE_EVENTS +
            "(" + COLUMN_ID + " INTEGER PRIMARY KEY," +
            COLUMN_EVENT_NAME+ " TEXT," +
            COLUMN_NUMBER_OF_PEOPLE + " INTEGER" + ")";

    // Expenses table create statement
    private static final String CREATE_TABLE_EXPENSES = "CREATE TABLE " + TABLE_EXPENSES +
            "(" + COLUMN_ID + " INTEGER PRIMARY KEY," +
            KEY_EVENT_ID + " INTEGER," +
            KEY_EXPENSE_NAME + " TEXT," +
            KEY_AMOUNT + " REAL" + ")";

    public TravelDBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // Create tables
        db.execSQL(CREATE_TABLE_EVENTS);
        db.execSQL(CREATE_TABLE_EXPENSES);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older tables if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_EVENTS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_EXPENSES);

        // Create tables again
        onCreate(db);
    }

    // CRUD Operations for Events

    // Add a new event
    public long addEvent(String eventName, int numberOfPeople) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_EVENT_NAME, eventName);
        values.put(COLUMN_NUMBER_OF_PEOPLE, numberOfPeople);
        long id = db.insert(TABLE_EVENTS, null, values);
        db.close();
        return id;
    }

    // Get all events
    @SuppressLint("Range")
    public List<TravelEvent> getAllEvents() {
        List<TravelEvent> events = new ArrayList<>();
        String selectQuery = "SELECT * FROM " + TABLE_EVENTS;
        SQLiteDatabase db = this.getWritableDatabase();

        Cursor cursor = db.rawQuery(selectQuery, null);
        if (cursor.moveToFirst()) {
            do {
                // 创建 TravelEvent 对象并设置属性
                TravelEvent event = new TravelEvent(
                        cursor.getInt(cursor.getColumnIndex(COLUMN_ID)), cursor.getString(cursor.getColumnIndex(COLUMN_EVENT_NAME)), cursor.getInt(cursor.getColumnIndex(COLUMN_NUMBER_OF_PEOPLE))
                );
                event.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
                event.setName(cursor.getString(cursor.getColumnIndex(COLUMN_EVENT_NAME)));
                event.setNumberOfPeople(cursor.getInt(cursor.getColumnIndex(COLUMN_NUMBER_OF_PEOPLE)));
                events.add(event);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return events;
    }

    // CRUD Operations for Expenses

    // Add a new expense
    public long addExpense(long eventId, String expenseName, double amount) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        //values.put(COLUMN_ID,Id);
        values.put(KEY_EVENT_ID, eventId);
        values.put(KEY_EXPENSE_NAME, expenseName);
        values.put(KEY_AMOUNT, amount);
        long id = db.insert(TABLE_EXPENSES, null, values);
        db.close();
        return id;
    }

    // Get all expenses for a particular event
    @SuppressLint("Range")
    public List<Expense> getExpensesForEvent(long eventId) {
        List<Expense> expenses = new ArrayList<>();
        String selectQuery = "SELECT * FROM " + TABLE_EXPENSES + " WHERE " + KEY_EVENT_ID + " = " + eventId;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        if (cursor.moveToFirst()) {
            do {
                Expense expense = new Expense(
                        cursor.getInt(cursor.getColumnIndex(COLUMN_ID)),
                        cursor.getInt(cursor.getColumnIndex(KEY_EVENT_ID)), // Update to getLong for event ID
                        cursor.getString(cursor.getColumnIndex(KEY_EXPENSE_NAME)),
                        cursor.getDouble(cursor.getColumnIndex(KEY_AMOUNT))
                );
                // Remove redundant setters
                expenses.add(expense);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return expenses;
    }

}


